<?php
namespace Admin\Models\log;

use Admin\Models\BaseModel;
use Phalcon\Db\Adapter\Pdo\Mysql as MysqlPdo;
use Admin\Models\sys\XhsysConfig;
use Phalcon\Mvc\Model\Query\Builder;
use Phalcon\Mvc\Model\Query;
use Phalcon\Mvc\Model\Manager as ModelsManager;

class DeviceLoginLog extends BaseModel
{

    /**
     *
     * @var integer
     * @Primary
     * @Identity
     * @Column(type="integer", length=20, nullable=false)
     */
    public $uid;

    /**
     *
     * @var integer
     * @Column(type="integer", length=20, nullable=true)
     */
    public $userid;

    /**
     *
     * @var string
     * @Column(type="string", length=100, nullable=true)
     */
    public $username;

    /**
     *
     * @var string
     * @Column(type="string", length=100, nullable=true)
     */
    public $logonname;

    /**
     *
     * @var string
     * @Column(type="string", length=200, nullable=true)
     */
    public $louid;

    /**
     *
     * @var string
     * @Column(type="string", length=20, nullable=true)
     */
    public $schoolid;

    /**
     *
     * @var string
     * @Column(type="string", length=250, nullable=true)
     */
    public $schoolname;

    /**
     *
     * @var string
     * @Column(type="string", length=200, nullable=true)
     */
    public $gradename;

    /**
     *
     * @var string
     * @Column(type="string", length=200, nullable=true)
     */
    public $devicenumber;

    /**
     *
     * @var string
     * @Column(type="string", length=200, nullable=true)
     */
    public $model;

    /**
     *
     * @var string
     * @Column(type="string", length=200, nullable=true)
     */
    public $lodate;

    /**
     *
     * @var string
     * @Column(type="string", length=20, nullable=true)
     */
    public $islogin;

    /**
     *
     * @var string
     * @Column(type="string", length=20, nullable=true)
     */
    public $isapplog;

    /**
     *
     * @var string
     * @Column(type="string", length=200, nullable=true)
     */
    public $datetime;

    /**
     * Initialize method for model.
     */
    public function initialize()
    {
        parent::initialize();
        $config = XhsysConfig::findFirst("sConfigName = 'oldCenterDB' AND bDelete = 0");
        $configData = json_decode($config->mConfigValue);
        $this->di->set(
            'dbMysql',
            function () use ($configData) {
                return new MysqlPdo(
                    [
                        'host'     => $configData->host,
                        'username' => $configData->username,
                        'password' => $configData->password,
                        'dbname'   => $configData->dbname,
                        'charset'  => 'utf8',
                    ]
                );
            }
        );

        $this->setConnectionService('dbMysql');

         $this->setSchema("xh_elasticsearch");
        // $this->setSource("device_user_login_log");
    }

    /**
     * Returns table name mapped in the model.
     *
     * @return string
     */
    public function getSource()
    {
        return 'device_user_login_log';
    }

    /**
     * Allows to query a set of records that match the specified conditions
     *
     * @param mixed $parameters
     * @return DeviceLoginLog[]|DeviceLoginLog|\Phalcon\Mvc\Model\ResultSetInterface
     */
    public static function find($parameters = null)
    {
        return parent::find($parameters);
    }

    /**
     * Allows to query the first record that match the specified conditions
     *
     * @param mixed $parameters
     * @return DeviceLoginLog|\Phalcon\Mvc\Model\ResultInterface
     */
    public static function findFirst($parameters = null)
    {
        return parent::findFirst($parameters);
    }

    /**
     * [getDataForDays description]
     * @method getDataForDays
     * @param  [type]         $params [获取7天前~今天的登录记录]
     * @return [type]                 [description]
     */
    public static function getDataForDays($params = null){
        $query = DeviceLoginLog::query()->columns(['SUM(islogin) AS islogin','SUM(isapplog) AS isapp','datetime'])->orderby('datetime asc')->groupBy('datetime');
        if(!isset($params['curTime'])){
          $params['curTime'] = date('Y-m-d',time());
        }

        if(!isset($params['days'])){
          $params['days'] = 7;
        }
        $days = '-'.trim($params['days']).' day';
        $firstDay = date('Y-m-d', strtotime($params['curTime'] . $days));
        $query->betweenWhere('datetime',$firstDay,$params['curTime']);

        $result = $query->execute();

        $data = array();
        if(!empty($result)){
          $tempArr = $result->toArray();
          $data['islogin'] = array_column($tempArr, 'islogin');
          $data['isapp'] = array_column($tempArr, 'isapp');
          $data['xList'] = array_column($tempArr, 'datetime');
          $data['series'] = array();
          array_push($data['series'],array('name' => '登录', 'source' => 'islogin','smooth' => true));
          array_push($data['series'],array('name' => '访问','source' => 'isapp','smooth' => true));
        }
        return $data;
    }

    /**
     * [getUsersCount 提供折线图页面标题所用数据]
     * @method getUsersCount
     * @return [type]        [最近一天之 用户数量/设备数量/学校数量]
     */
    public static function getAllCount(){
      $query = DeviceLoginLog::query()->columns(['COUNT(DISTINCT userid) AS userCount','COUNT(DISTINCT devicenumber) AS deviCount','COUNT(DISTINCT schoolid) AS schoolCount'])->orderby('datetime desc')->groupBy('datetime')->limit(1,0);
      $result = $query->execute()->toArray();
      if(!empty($result)){
        $result = $result[0];
      }
      else{
        $result['userCount'] = $result['deviCount'] = $result['schoolCount'] = 0;
      }
      return $result;
    }

    /**
     * [getByAllClass 每个班未登录人之统计]
     * @method getByAllClass
     * @param  [string]      $params['mintime'] [起始时间]
     * @param  [string]      $params['maxtime']  [截止日期]
     * @return [array]                [返回时间段内每个班级未登录人之统计]
     */
    public function getByAllClass($params = null){
      $query = DeviceLoginLog::query()
      ->columns(['gradename','schoolname','COUNT(DISTINCT userid) AS alluser'])
      ->groupBy('gradename')
      ->orderby('gradename desc');
      $result = $query->execute()->toArray();

      $classes = array_column($result,'gradename');
      $classesStr = implode('","',$classes);
      //查询每个班未操作的人数
      $tempq = 'gradename IN ("'.$classesStr.'")';

      if(isset($params['maxtime']) && strcmp($params['maxtime'],'')){
        $maxtime = date('Y-m-d',strtotime($params['maxtime']));
      }
      else{
        $maxtime = date('Y-m-d',time());
      }

      if(isset($params['mintime']) && strcmp($params['mintime'],'')){
        $mintime = date('Y-m-d',strtotime($params['mintime']));
      }
      else{
        $mintime = date('Y-m-d',strtotime($maxtime .'-1 day'));
      }
      $sql = "SELECT
        COUNT(nodo.uid) AS nologin,
        nodo.gradename,
        nodo.schoolid,
        nodo.schoolname,
        nodo.gradename AS gname
        FROM
        (
          SELECT
            gradename,
            schoolname,
            uid,
            schoolid,
            lodate
          FROM
            device_user_login_log
          WHERE
            ".$tempq."
          AND
            datetime <= '".$maxtime."'
          GROUP BY
            userid
          HAVING
            MAX(lodate) < '".$mintime."'
          OR MAX(lodate) IS NULL
          ORDER BY
            gradename DESC
        ) AS nodo
        GROUP BY nodo.gradename
        ORDER BY nodo.gradename desc";

      try {
        $res = $this->di->get('dbMysql')->fetchAll($sql);
      } catch (Exception $e) {
        echo $e->getMessage();
      }

      $arrForNoDo = array_column($res,'nologin','gradename');

      foreach ($result as $key => $value) {
        $result[$key]['nologin'] = (isset($arrForNoDo[$value['gradename']]) ? $arrForNoDo[$value['gradename']] : 0);
      }
      //$allnologin = array_sum(array_column($result,'nologin'));
      //$allusers = array_sum(array_column($result,'alluser'));
      $allnologin = 0;
      $allusers = 0;

      $data = array();
      $data['rows'] = $result;
      $data['footer'] = [
        [
          'gradename' => '总计',
          'nologin' => $allnologin,
          'alluser' => $allusers
        ]
      ];

      return $data;
    }

    /**
     * [getByGrades 获取未操作人员详细名单]
     * @method getByGrades
     * @param  [string]      $params['grades']   [年级名单(逗号隔开)]
     * @param  [string]      $params['mintime'] [起始时间]
     * @param  [string]      $params['maxtime']  [截止日期]
     * @param  [string]      $params['total']  [列表1返回的具体人数]
     * @return [array]              [详细未操作人员名单]
     */
    public function getByGrades($params = null){
      if(isset($params['grades']) && !empty($params['grades'])){
        $classArr = explode(',',$params['grades']);
        $classArr = array_filter($classArr);
        $classesStr = implode('","',$classArr);
        if(isset($params['maxtime']) && strcmp($params['maxtime'],'')){
          $maxtime = date('Y-m-d',strtotime($params['maxtime']));
        }
        else{
          $maxtime = date('Y-m-d',time());
        }

        if(isset($params['mintime']) && strcmp($params['mintime'],'')){
          $mintime = date('Y-m-d',strtotime($params['mintime']));
        }
        else{
          $mintime = date('Y-m-d',strtotime($maxtime .'-1 day'));
        }

        if(isset($params['page'])){
            $page = $params['page'];
        }
        else{
            $page = 1;
        }

        if(isset($params['rows'])){
            $rows = intval($params['rows']);
        }
        else{
            $rows = 10;
        }
        $temppage = 'LIMIT '.$rows.' OFFSET '.($page-1)*$rows;


        $tempq = 'gradename IN ("'.$classesStr.'")';
        $sql = "
            SELECT
              *
            FROM
              device_user_login_log
            WHERE
              ".$tempq."
            AND
              datetime <= '".$maxtime."'
            GROUP BY
              userid
            HAVING
              MAX(lodate) < '".$mintime."'
            OR MAX(lodate) IS NULL
            ORDER BY
              gradename ASC ".$temppage;

          $res = $this->di->get('dbMysql')->fetchAll($sql);
          $data = array();
          $data['rows'] = $res;
          $data['total'] = $params['total'];
      }
      else{
        $data = array();
      }
      return $data;
    }

}
